package com.milk.Dao;

import com.milk.DB.DBConnection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

public class ReportDao {
    // 按时间段统计销售数量（示例为按天统计，可修改SQL语句实现按其他时间段，如按月、按年等）
    public static Map<String, Integer> countSalesByDay(String startDate, String endDate) {
        Map<String, Integer> salesCountMap = new HashMap<>();
        String sql = "SELECT DATE(order_time) as order_date, COUNT(*) as sales_count " +
                "FROM orders " +
                "WHERE order_time BETWEEN? AND? " +
                "GROUP BY DATE(order_time)";
        try (Connection connection = DBConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, startDate);
            statement.setString(2, endDate);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                salesCountMap.put(resultSet.getString("order_date"), resultSet.getInt("sales_count"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return salesCountMap;
    }

    // 按饮品分类统计销售金额
    public static Map<String, Double> sumSalesByCategory() {
        Map<String, Double> salesAmountMap = new HashMap<>();
        String sql = "SELECT m.category, SUM(m.price) as total_amount " +
                "FROM milk_tea_drinks m " +
                "JOIN orders o ON FIND_IN_SET(m.id, o.drink_ids) > 0 " +
                "GROUP BY m.category";
        try (Connection connection = DBConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                salesAmountMap.put(resultSet.getString("category"), resultSet.getDouble("total_amount"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return salesAmountMap;
    }
}